/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package model;

/**
 *
 * @author aeguzkiza
 */

import DBConnections.MySQL_Query;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import controller.Controller;
import java.util.List;

public class Consultation {
    MySQL_Query DB = new MySQL_Query();     // Functions related with MySQL connections.
    Controller C = new Controller();
    private ResultSet rs;   // SQL Query resulset.
    final int ORDER_BY_NAME = 0;
    final int ORDER_BY_NAME_AND_STUDY = 1;
    final int ORDER_BY_NAME_AND_WORKLIST = 2;
    final int ORDER_BY_STUDY = 3;
    final int ORDER_BY_PHYSICIAN = 4;
    final int ORDER_BY_AET = 5;
    final int ORDER_BY_ID = 6;
    
    private static String noIdGenerated = "\nError al escribir en la base de datos:"
                                        + "\nEs posible que se esté intentando escribir información que no puede ser duplicada."
                                        + "\nCambie el valor repetido y vuelva a intentar guardar más adelante.";
    

    public interface ConsultationListener {
        void updateAddPatient(Integer patientId);
        void updateAddStudy(Integer patientId, Integer studyId);
        void updateAddPhysician(Integer studyId, Integer physicianId);
    }
           
    private String getSQLOrder(final int flag){
        String order;
        switch(flag){
            case ORDER_BY_NAME: order = "PatientsName,PatientsSurName";break;
            case ORDER_BY_NAME_AND_STUDY: order = "PatientsName,PatientsSurName,StudyDate";break;
            case ORDER_BY_NAME_AND_WORKLIST: order = "PatientsName,PatientsSurName,ScheduledProcedureStepDateTime";break;
            case ORDER_BY_STUDY: order = "StudyDate";break;
            case ORDER_BY_PHYSICIAN: order = "PerformingPhysiciansName,PerformingPhysiciansSurName";break;
            case ORDER_BY_AET: order = "AETitle";break;
            case ORDER_BY_ID: order = "Id";break;
            default: order = "PatientsName,PatientsSurName,StudyDate";break;
        }
        return order;
    }

    // Global usage functions:
    public ArrayList<String> getKeys(String tablename) throws SQLException {    // Get the list of Keys from any table in MySQL.
        DB = new MySQL_Query();
        System.out.println("\n========== Start of getKeys ==========");
        ArrayList<String> Keys = new ArrayList();

        // One query to get all patient and study list available in database.
        rs = DB.SQLquery("SELECT "+tablename+".* FROM "+tablename);
        Keys = DB.getColumnNames(rs);
        //System.out.println("studyKeys: "+studyKeys);
        DB.closeConnection();
        return Keys;
    }

    public Integer add(DB2Hash hash, String tableName, boolean IsGetGeneratedKeys) throws SQLException {
        DB = new MySQL_Query();
        ArrayList v = hash.valuesSQLinsert();
        String k = hash.keysSQLinsert();
        String q = hash.questionSQLinsert();
        Integer Id = null;
        //System.out.println("INSERT IGNORE INTO patient ("+k+") VALUES ("+q+")");
        
        // Add new patient into database.
        System.out.println("WRITTING DATA...");

        DB.SQLupdate2("INSERT IGNORE INTO "+tableName+" ("+k+") VALUES ("+q+")",IsGetGeneratedKeys,v.size(),v);
        if(IsGetGeneratedKeys == true){
            ResultSet rsUpdate = DB.GeneratedKeys();
            boolean isUpdated = rsUpdate.first();
            System.out.println(tableName+" IsUpdated: "+isUpdated);
            if(isUpdated == true){              // If there is interaction with the database, takes the new Id value.
                Id = rsUpdate.getInt(1);     // Takes the first value of "DB.GeneratedKeys()".
            }else{
                //throw new SQLException(noIdGenerated);
                //throw new IllegalArgumentException("There's no interaction with database. The new Patient haven't been writted");                
            }
        }

        DB.closeConnection();        
        return Id;
    }
    public void update(DB2Hash hash, String tableName) throws SQLException {
        DB = new MySQL_Query();
        System.out.println("Id from hash (update): "+hash.get("Id"));
        ArrayList v = hash.valuesSQLinsert();
        String k = hash.keysSQLupdate();
        //System.out.println("UPDATE "+tableName+" SET ("+k+")");

        // Add new patient into database.
        System.out.println("UPDATE PATIENT DATA...");

        DB.SQLupdate2("UPDATE "+tableName+" SET "+k+" WHERE id="+hash.get("Id"),true,v.size(),v);
        /*DB.SQLupdate2("INSERT IGNORE INTO patient (Id,PatientID,PatientsName,PatientsSurName,PatientsSex,PatientsBirthDate,PatientComments) VALUES (?,?,?,?,?,?,?,?) ON DUPLICATE KEY UPDATE",true,patient.size(),patient);
        ResultSet Id = DB.GeneratedKeys();

        DB.SQLupdate2("UPDATE patient SET PatientID=?,PatientsName=?,PatientsSurName=?,PatientsSex=?,PatientsBirthDate=?,PatientComments=? WHERE iD="+Id,true,patient.size(),patient);
*/
        DB.closeConnection();
        
    }
    public Integer replace(DB2Hash hash, String tableName) throws SQLException { // Not recommended -> The replace command deletes the row and then creates the new one. If there is error during the process, removes a row in the DB!
        DB = new MySQL_Query();
        // Testing REPLACE MySQL function.
        ArrayList v = hash.valuesSQLinsert();
        String k = hash.keysSQLinsert();
        String q = hash.questionSQLinsert();
        Integer Id = null;
        //System.out.println("INSERT IGNORE INTO patient ("+k+") VALUES ("+q+")");

        // Add new patient into database.
        System.out.println("WRITTING PATIENT DATA...");

        DB.SQLupdate2("REPLACE INTO "+tableName+" ("+k+") VALUES ("+q+")",true,v.size(),v);
        ResultSet rsUpdate = DB.GeneratedKeys();
        boolean isUpdated = rsUpdate.first();

        if(isUpdated == true){              // If there is interaction with the database, takes the new Id value.
            Id = rsUpdate.getInt(1);     // Takes the first value of "DB.GeneratedKeys()".
        }else{
            throw new SQLException(noIdGenerated);
            //throw new IllegalArgumentException("There's no interaction with database. The new Patient haven't been writted");
        }

        DB.closeConnection();

        return Id;
    }
    public void truncate(String tableName) throws SQLException {    // NOT USED YET.
        DB = new MySQL_Query();
        // Add new patient into database.
        System.out.println("DELETING DATA...");
        ArrayList v = new ArrayList();
        DB.SQLupdate2("TRUNCATE TABLE "+tableName,false,v.size(),v);
        DB.closeConnection();
        
    }
    public void delete(String tableName) throws SQLException {
        DB = new MySQL_Query();
        // Add new patient into database.
        System.out.println("DELETING DATA...");
        ArrayList v = new ArrayList();
        DB.SQLupdate2("DELETE FROM "+tableName,false,v.size(),v);
        DB.closeConnection();
        
    }
    public void delete(Integer Id, String tableName) throws SQLException {
        DB = new MySQL_Query();
        // Add new patient into database.
        System.out.println("DELETING DATA...");
        ArrayList v = new ArrayList();
        v.add(Id);
        DB.SQLupdate2("DELETE FROM "+tableName+" WHERE Id=?",false,v.size(),v);
        DB.closeConnection();
        
    }

    /*public Integer getFirstId(String tablename) throws SQLException, Exception {
        // Gets the first Id from a specific table.
        DB = new MySQL_Query();
        Integer Id = null;
        
        rs = DB.SQLquery("SELECT "+tablename+".Id AS Id FROM "+tablename);
        rs.first();
        Id = rs.getInt("Id");
        DB.closeConnection();
        
        return Id;
    }*/
    public Integer getId(String tablename,String var,String value) throws SQLException {
        DB = new MySQL_Query();
        Integer Id = null;

        System.out.println("\n========== Start of checkHCE ==========");
        rs = DB.SQLquery("SELECT "+tablename+".Id AS Id FROM "+tablename+" WHERE "+var+"='"+value+"'");
        rs.beforeFirst();

        while(rs.next()){
            Id = rs.getInt("Id");
        }

        DB.closeConnection();
        return Id;
    }
    
    public ArrayList<String> getAttributeList(String tablename, String attributename, final int flag) throws SQLException, Exception {
        DB = new MySQL_Query();
        ArrayList<String> List = new ArrayList();
        String order = getSQLOrder(flag);
        
        // One query to get all patient available in database.
        //rs = DB.SQLquery("SELECT patient.Id AS Id FROM patient");
        rs = DB.SQLquery("SELECT "+tablename+"."+attributename+" AS "+attributename+" FROM "+tablename+" ORDER BY "+order);

        //rs = DB.SQLquery("SELECT patient.Id FROM patient,study,consultation,physician,worklist WHERE Patient_Id = patient.Id AND Study_Id = Study.Id AND Physician_Id = physician.Id AND WorkList_Id = worklist.Id GROUP BY Patient.Id ORDER BY "+order);
        rs.beforeFirst();

        while(rs.next()){
            List.add(rs.getString(attributename));
        }

        DB.closeConnection();
        return List;
    }

    public ArrayList<Integer> getList(String tablename, final int flag) throws SQLException, Exception {
        DB = new MySQL_Query();
        ArrayList<Integer> List = new ArrayList();
        String order = getSQLOrder(flag);
        
        // One query to get all patient available in database.
        //rs = DB.SQLquery("SELECT patient.Id AS Id FROM patient");
        rs = DB.SQLquery("SELECT "+tablename+".Id AS Id FROM "+tablename+" ORDER BY "+order);

        //rs = DB.SQLquery("SELECT patient.Id FROM patient,study,consultation,physician,worklist WHERE Patient_Id = patient.Id AND Study_Id = Study.Id AND Physician_Id = physician.Id AND WorkList_Id = worklist.Id GROUP BY Patient.Id ORDER BY "+order);
        rs.beforeFirst();

        while(rs.next()){
            List.add(rs.getInt("Id"));
        }

        DB.closeConnection();
        return List;
    }
    public ArrayList<Integer> getList(String tablename, List <String> var, String value, final int flag) throws SQLException, Exception {
        DB = new MySQL_Query();
        ArrayList<Integer> List = new ArrayList();
        String order = getSQLOrder(flag);
        String filter = "WHERE ";
        if(var.size() == 1){
            filter += var.get(0)+" LIKE '%"+value+"%'";
        }else{
            filter = "CONCAT("+var.get(0);
            for(int i=1; i<var.size();i++){
                filter += ",' ',"+var.get(i);
                if(i >= var.size()-1){
                    filter += ") LIKE '%"+value+"%'";
                }
            }
        }
        // One query to get all patient available in database.
        //rs = DB.SQLquery("SELECT patient.Id AS Id FROM patient");
        rs = DB.SQLquery("SELECT "+tablename+".Id AS Id FROM "+tablename+" "+filter+" ORDER BY "+order);

        //rs = DB.SQLquery("SELECT patient.Id FROM patient,study,consultation,physician,worklist WHERE Patient_Id = patient.Id AND Study_Id = Study.Id AND Physician_Id = physician.Id AND WorkList_Id = worklist.Id GROUP BY Patient.Id ORDER BY "+order);
        rs.beforeFirst();

        while(rs.next()){
            List.add(rs.getInt("Id"));
        }

        DB.closeConnection();
        return List;
    }
    public ArrayList<Integer> getList(String tablename, String tablename2, Integer Id, final int flag) throws SQLException, Exception {
        DB = new MySQL_Query();
        // Get a study List from each selected Patient.
        ArrayList<Integer> List = new ArrayList();
        String order = getSQLOrder(flag);
        
        // Query to get all study list available in database for one patient.
        rs = DB.SQLquery("SELECT "+tablename+"_Id AS Id FROM "+tablename+","+tablename2+",consultation WHERE "+tablename2+"_Id = "+Id+" AND "+tablename+"_Id = "+tablename+".Id AND "+tablename2+"_Id = "+tablename2+".Id ORDER BY "+order);
        rs.beforeFirst();

        while(rs.next()){
            List.add(rs.getInt("Id"));
        }

        DB.closeConnection();
        return List;
    }    
    public ArrayList<Integer> getAcquisitionList(Integer studyId, final int flag) throws SQLException, Exception { // Exceptions:
        DB = new MySQL_Query();
        // Get an acquisition List from each selected Study.
        ArrayList<Integer> acquisitionList = new ArrayList();
        String order = getSQLOrder(flag);
        
        // Query to get all acquisition list available in database for one study.
        rs = DB.SQLquery("SELECT acquisition.Id AS Id FROM study,acquisition WHERE study_Id = "+studyId+" AND study_Id = study.Id ORDER BY "+order);
        rs.beforeFirst();

        while(rs.next()){
            acquisitionList.add(rs.getInt("Id"));
        }

        DB.closeConnection();
        return acquisitionList;
    }
    public DB2Hash getAttributes(String tablename) throws SQLException, Exception {
        DB = new MySQL_Query();
        DB2Hash Hash = null;

        System.out.println("\n========== Start of getAtributes from the first row in the DB ==========");

        // One query to get all physician data available in database.
        rs = DB.SQLquery("SELECT "+tablename+".* FROM "+tablename);
        rs.first();

        // FILL PATIENT CLASS TABLE.
        Hash = new DB2Hash(rs);

        System.out.println("========== End of getAttributes ==========");


        DB.closeConnection();
        return Hash;//patient.get(patientId);
    }
    public DB2Hash getAttributes(String tablename, Integer Id) throws SQLException, Exception {
        DB = new MySQL_Query();
        DB2Hash Hash = null;
        
        System.out.println("\n========== Start of get ==========");

        // One query to get all physician data available in database.
        rs = DB.SQLquery("SELECT "+tablename+".* FROM "+tablename+" WHERE "+tablename+".Id='"+Id+"'");
        rs.first();

        // FILL PATIENT CLASS TABLE.
        Hash = new DB2Hash(rs);

        System.out.println("========== End of getPatient ==========");

        
        DB.closeConnection();
        return Hash;//patient.get(patientId);
    }
    public DB2Hash getAttributes(String tablename, String tablename2, Integer Id) throws SQLException, Exception {
        DB = new MySQL_Query();
        // Get attributes in a table, on the case of coincidents Ids in consultation.
        DB2Hash Hash = null;
        
        System.out.println("\n========== Start of get ==========");

        // One query to get all physician data available in database.
        rs = DB.SQLquery("SELECT "+tablename+".* FROM "+tablename+","+tablename2+",consultation WHERE "+tablename2+".Id='"+Id+"' AND "+tablename+".Id="+tablename+"_Id AND "+tablename2+".Id="+tablename2+"_Id");
        rs.first();

        // FILL PATIENT CLASS TABLE.
        Hash = new DB2Hash(rs);

        System.out.println("========== End of getPatient ==========");
        
        DB.closeConnection();
        return Hash;//patient.get(patientId);
    }
    
    public ArrayList<DB2Hash> getAttributes_Id(String tablename, String attributeName, Integer attributeValue) throws SQLException, Exception {
        DB = new MySQL_Query();
        // Get attributes in a table, on the case of coincidents Ids in consultation.
        ArrayList<DB2Hash> HashList = new ArrayList();
        DB2Hash Hash = null;

        System.out.println("\n========== Start of get ==========");

        // One query to get all physician data available in database.
        rs = DB.SQLquery("SELECT "+tablename+".* FROM "+tablename+" WHERE "+attributeName+"_Id='"+attributeValue+"'");
        rs.beforeFirst();

        while(rs.next()){
            Hash = new DB2Hash(rs);
            HashList.add(Hash);
        }        

        System.out.println("========== End of getPatient ==========");

        DB.closeConnection();
        return HashList;//patient.get(patientId);
    }
    
    /*public DB2Hash getAttributes(String tablename, String tablename2, String var, String value) throws SQLException, Exception {
        // Get attributes in a table, if a value that's no a key, coincides in two tables.
        // There is no table-connection between attributes.
        DB = new MySQL_Query();
        DB2Hash Hash = null;
        
        System.out.println("\n========== Start of get ==========");

        // One query to get all physician data available in database.
        rs = DB.SQLquery("SELECT "+tablename+".* FROM "+tablename+","+tablename2+",consultation WHERE "+tablename+"."+var+"='"+value+"' AND "+tablename+".Id="+tablename+"_Id AND "+tablename2+".Id="+tablename2+"_Id");
        rs.first();

        // FILL PATIENT CLASS TABLE.
        Hash = new DB2Hash(rs);

        System.out.println("========== End of getPatient ==========");

        DB.closeConnection();
        return Hash;//patient.get(patientId);
    }*/
       
    

    /*public ArrayList<Integer> getWorklistPatientList(final int flag){   // !!! NOT USED
        ArrayList<Integer> patientList = new ArrayList();
        String order = getSQLOrder(flag);
        try {            
            // One query to get all patient available in database.
            //rs = DB.SQLquery("SELECT patient.Id AS Id FROM patient");
            rs = DB.SQLquery("SELECT patient.Id AS Id FROM patient,study,worklist,consultation WHERE Patient_Id = patient.Id AND Study_Id = Study.Id AND WorkList_Id = worklist.Id GROUP BY Id ORDER BY "+order);

            //rs = DB.SQLquery("SELECT patient.Id FROM patient,study,consultation,physician,worklist WHERE Patient_Id = patient.Id AND Study_Id = Study.Id AND Physician_Id = physician.Id AND WorkList_Id = worklist.Id GROUP BY Patient.Id ORDER BY "+order);
            rs.beforeFirst();
            
            while(rs.next()){                   
                patientList.add(rs.getInt("Id"));                
            }
        } catch (SQLException ex) {
            Logger.getLogger(Consultation.class.getName()).log(Level.SEVERE, null, ex);
        } catch (Exception ex) {
            Logger.getLogger(Consultation.class.getName()).log(Level.SEVERE, null, ex);
        }
        return patientList;
    }*/
    
    /*public DB2Hash getPhysician(Integer physicianId) {
        DB2Hash physicianHash = null;
        try {
            System.out.println("\n========== Start of getPhysician ==========");
            //patient.get(physicianId);   // Get patient previously added on initJtree.

            // One query to get all patient and study list available in database.
            rs = DB.SQLquery("SELECT physician.* FROM physician WHERE physician.Id='"+physicianId+"'");
            rs.first();

            // FILL PATIENT CLASS TABLE.
            physicianHash = new DB2Hash(rs);

            System.out.println("========== End of getPhysician ==========");

        } catch (SQLException ex) {
            Logger.getLogger(Consultation.class.getName()).log(Level.SEVERE, null, ex);
        } catch (Exception ex) {
            Logger.getLogger(Consultation.class.getName()).log(Level.SEVERE, null, ex);
        }

        return physicianHash;
    }*/

    /*public void addPatient(DB2Hash patient) throws SQLException {
        patient = new DB2Hash();
        patient.put("PatientsName", "Miguel");
        patient.put("PatientsSurName", "Mtz");
        String[] k = patient.keysSQLinsert();
        String[] v = patient.valuesSQLinsert();
        System.out.println("k: "+k);
        System.out.println("v: "+v);
        
        DB.SQLupdate(patient.printSQLinsert("patient"), false, 0, null);
        /*DB.SQLupdate("INSERT IGNORE INTO patient ("+?,?,?,?,?+") VALUES("+?,?,?,?,?+")",false,v.length,v);
        String[] var = new String[]{null,PatientID,PatientsName,PatientsSurName,PatientsSex,df.format(PatientsBirthDate),null};
        DB.SQLupdate("INSERT IGNORE INTO patient (?,?,?,?,?) VALUES(?,?,?,?,?)",false,var.length,var);*//*
        int i;
        for ( i = 0; i < consultationListener.size(); i++ ) {
            consultationListener.get(i).updateAddPatient(Integer.parseInt(patient.get("patient.Id")));
        }
    }*/
    
    /*public void addStudy(int patientId, int studyId) {

        //this.study[patientList].add(study.get("study.Id")); // TODO: Save studies for each patient in the same row.
        this.patient.add(studyId);
        int i;
        for ( i = 0; i < consultationListener.size(); i++ ) {
            consultationListener.get(i).updateAddStudy(study);
        }
    }
    
    public void addPhysician(DB2Hash physician) {
        this.physician.add(physician);

        int i;
        for ( i = 0; i < consultationListener.size(); i++ ) {
            consultationListener.get(i).updateAddPhysician(physician);
        }
    }
    public String getPhysicianId(int index) {
        DB2Hash treeHash = physician.get(index);
        String physicianId = treeHash.get("physician.Id");
        System.out.println("Physician id: "+physicianId);
        return physicianId;
    }*/
    
    
}